﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace UpLoad
{
    public class FileDal
    {
        private string conn = Config.ConnectionString;

        public bool AddFile(FileInfoModel model)
        {
            StringBuilder str = new StringBuilder();
            str.Append(" insert into FileInfo values(");
            str.Append(" @filename,");
            str.Append(" @filenameA,");
            str.Append(" @filenameB,");
            str.Append(" @filetype,");
            str.Append(" @createtime,");
            str.Append(" @updatetime,");
            str.Append(" @foreignkeyA,");
            str.Append(" @foreignkeyB");
            str.Append(" ) ");

            SqlParameter[] parameters = {
					new SqlParameter("@filename", SqlDbType.NVarChar,200),
					new SqlParameter("@filenameA", SqlDbType.NVarChar,200),
					new SqlParameter("@filenameB", SqlDbType.NVarChar,200),
					new SqlParameter("@filetype", SqlDbType.NVarChar,50),
					new SqlParameter("@createtime", SqlDbType.DateTime),
					new SqlParameter("@updatetime", SqlDbType.DateTime),
					new SqlParameter("@foreignkeyA", SqlDbType.Int,4),
					new SqlParameter("@foreignkeyB", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.filename;
            parameters[1].Value = model.filenameA;
            parameters[2].Value = model.filenameB;
            parameters[3].Value = model.filetype;
            parameters[4].Value = model.createtime;
            parameters[5].Value = model.updatetime;
            parameters[6].Value = model.foreignkeyA;
            parameters[7].Value = model.foreignkeyB;

            int rows = SQLHelper.ExecuteSql(str.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateFile(FileInfoModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update FileInfo set ");
            strSql.Append("filename=@filename,");
            strSql.Append("filenameA=@filenameA,");
            strSql.Append("filenameB=@filenameB,");
            strSql.Append("filetype=@filetype,");
            strSql.Append("createtime=@createtime,");
            strSql.Append("updatetime=@updatetime,");
            strSql.Append("foreignkeyA=@foreignkeyA,");
            strSql.Append("foreignkeyB=@foreignkeyB");
            strSql.Append(" where foreignkeyA=@foreignkeyA or foreignkeyB=@foreignkeyB ");
            SqlParameter[] parameters = {
					new SqlParameter("@filename", SqlDbType.NVarChar,200),
					new SqlParameter("@filenameA", SqlDbType.NVarChar,200),
					new SqlParameter("@filenameB", SqlDbType.NVarChar,200),
					new SqlParameter("@filetype", SqlDbType.NVarChar,50),
					new SqlParameter("@createtime", SqlDbType.DateTime),
					new SqlParameter("@updatetime", SqlDbType.DateTime),
					new SqlParameter("@foreignkeyA", SqlDbType.Int,4),
					new SqlParameter("@foreignkeyB", SqlDbType.NVarChar,200)};
            parameters[0].Value = model.filename;
            parameters[1].Value = model.filenameA;
            parameters[2].Value = model.filenameB;
            parameters[3].Value = model.filetype;
            parameters[4].Value = model.createtime;
            parameters[5].Value = model.updatetime;
            parameters[6].Value = model.foreignkeyA;
            parameters[7].Value = model.foreignkeyB;

            int rows = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        // <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteByForeignKey(int foreignkeyA)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from FileInfo ");
            strSql.Append(" where foreignkeyA=@foreignkeyA ");
            SqlParameter[] parameters = {
					new SqlParameter("@foreignkeyA", SqlDbType.Int,4) };
            parameters[0].Value = foreignkeyA;

            int rows = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }  

        public bool DeleteByForeignKey(string foreignkeyB)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from FileInfo ");
            strSql.Append(" where foreignkeyB=@foreignkeyB ");
            SqlParameter[] parameters = {
					new SqlParameter("@foreignkeyA", SqlDbType.NVarChar,200) };
            parameters[0].Value = foreignkeyB;

            int rows = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Delete(string filename)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from FileInfo ");
            strSql.Append(" where filename=@filename ");
            SqlParameter[] parameters = {
					new SqlParameter("@filename", SqlDbType.NVarChar,200) };
            parameters[0].Value = filename;

            int rows = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public List<FileInfoModel> GetAllFiles()
        {
            List<FileInfoModel> list = new List<FileInfoModel>();
            string str = "SELECT * FROM dbo.FileInfo";
            SqlDataReader reader = SQLHelper.ExecuteReader(conn, CommandType.Text,str);
            if (reader != null)
            {
                while (reader.Read())
                {
                    FileInfoModel model = new FileInfoModel();
                    if (reader["filename"] != null) model.filename = reader["filename"].ToString();
                    if (reader["filetype"] != null) model.filetype = reader["filetype"].ToString();
                    if (reader["filenameA"] != null) model.filenameA = reader["filenameA"].ToString();
                    if (reader["filenameB"] != null) model.filenameB = reader["filenameB"].ToString();
                    if (reader["createtime"] != null) model.createtime = DateTime.Parse(reader["createtime"].ToString());
                    if (reader["updatetime"] != null) model.updatetime = DateTime.Parse(reader["updatetime"].ToString());
                    if (reader["foreignkeyA"] != null) model.foreignkeyA = int.Parse(reader["foreignkeyA"].ToString());
                    if (reader["foreignkeyB"] != null) model.foreignkeyB = reader["foreignkeyB"].ToString();
                    list.Add(model);
                }
            }
            return list;
        }
    }
}
